Detailed Explanation of PHP Product Spike Problem Solution Example [mysql and redis]

This paper describes the solution of PHP product spike problem with examples. Share it for your reference, as follows:

Introduction

Assume that num is a field stored in the database, which saves the remaining number of products killed.

if($num > 0){
  // The user snapped up successfully and recorded the user information 
  $num--;
}

Assuming that in a scenario with high concurrency, when the value of num in the database is 1, multiple processes may read that num is 1 at the same time. The program judges that it meets the conditions and snaps up successfully, and num is minus 1. This will lead to over-issuance of goods. Originally, there were only 10 goods that could be snapped up, and more than 10 people might grab them. At this time, num is negative after snapping up.

There are many solutions to solve this problem, which can be simply divided into solutions based on mysql and redis. The performance of redis is due to mysql, so it can carry higher concurrency. However, the solutions introduced below are all based on a single mysql and redis, and higher concurrency requires distributed solutions, which are not covered in this paper.

Solution based on mysql

Commodity list goods

CREATE TABLE `goods` (
 `id` int(11) NOT NULL,
 `num` int(11) DEFAULT NULL,
 `version` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Snap-up result table log

CREATE TABLE `log` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `good_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Pessimistic lock

The pessimistic lock scheme uses exclusive reading, that is, only one process can read the value of num at the same time. After the transaction is committed or rolled back, the lock will be released and other processes can read it. This scheme is the simplest and easiest to understand, and can be directly adopted when the performance requirements are not high. Note that SELECT … FOR UPDATE should use indexes as much as possible in order to lock as few rows as possible; Exclusive locks are released after the transaction is executed, not after the read is completed, so the transactions used should be committed or rolled back as early as possible to release exclusive locks earlier.

$this->mysqli->begin_transaction();
$result = $this->mysqli->query("SELECT num FROM goods WHERE id=1 LIMIT 1 FOR UPDATE");
$row = $result->fetch_assoc();
$num = intval($row['num']);
if($num > 0){
  usleep(100);
  $this->mysqli->query("UPDATE goods SET num=num-1");
  $affected_rows = $this->mysqli->affected_rows;
  if($affected_rows == 1){
    $this->mysqli->query("INSERT INTO log(good_id) VALUES({$num})");
    $affected_rows = $this->mysqli->affected_rows;
    if($affected_rows == 1){
      $this->mysqli->commit();
      echo "success:".$num;
    }else{
      $this->mysqli->rollback();
      echo "fail1:".$num;
    }
  }else{
    $this->mysqli->rollback();
    echo "fail2:".$num;
  }
}else{
  $this->mysqli->commit();
  echo "fail3:".$num;
}

Optimistic lock

The optimistic lock scheme does not add exclusive lock when reading data, but solves the problem by an version field that will increase itself every time it is updated. Multiple processes read the same num, and then they can update successfully. While each process reads num, the value of version is also read, and version is also updated while num is updated, and the equivalent judgment of version is added when updating. Assuming that 10 processes read num with a value of 1 and version with a value of 9, the update statements executed by these 10 processes are UPDATE goods SET num=num-1,version=version+1 WHERE version=9 However, when one of the processes is successfully executed, the value of version in the database will become 10, and the remaining nine processes will not be successfully executed, thus ensuring that the goods will not be oversold, and the value of num will not be less than 0, but this also leads to a problem, that is, the user who made the snapping request earlier may not be able to grab it, but will be grabbed by the later request.

$result = $this->mysqli->query("SELECT num,version FROM goods WHERE id=1 LIMIT 1");
$row = $result->fetch_assoc();
$num = intval($row['num']);
$version = intval($row['version']);
if($num > 0){
  usleep(100);
  $this->mysqli->begin_transaction();
  $this->mysqli->query("UPDATE goods SET num=num-1,version=version+1 WHERE version={$version}");
  $affected_rows = $this->mysqli->affected_rows;
  if($affected_rows == 1){
    $this->mysqli->query("INSERT INTO log(good_id) VALUES({$num})");
    $affected_rows = $this->mysqli->affected_rows;
    if($affected_rows == 1){
      $this->mysqli->commit();
      echo "success:".$num;
    }else{
      $this->mysqli->rollback();
      echo "fail1:".$num;
    }
  }else{
    $this->mysqli->rollback();
    echo "fail2:".$num;
  }
}else{
  echo "fail3:".$num;
}

where condition (atomic operation)

The pessimistic lock scheme ensures that the value of num in the database can only be read and processed by one process in the same time, that is, concurrent reading processes have to queue up here to execute in turn. Although the value of num can be read by multiple processes at the same time, the equivalent judgment of version in the update operation can ensure that only one concurrent update operation can succeed in the same time.

There is also a simpler scheme, which only adds num during the update operation > 0. Although the scheme restricted by where conditions seems to be similar to the optimistic lock scheme, which can prevent the occurrence of overshoot problem, the performance is quite different when num is large. If num is 10 at this time, and five processes read num=10 at the same time, for the optimistic lock scheme, only one of these five processes will be updated successfully due to the equivalent judgment of version field, and num is 9 after the execution of these five processes is completed; For the scheme of where condition judgment, as long as num > 0 can be updated successfully, and num is 5 after the execution of these five processes is completed.

$result = $this->mysqli->query("SELECT num FROM goods WHERE id=1 LIMIT 1");
$row = $result->fetch_assoc();
$num = intval($row['num']);
if($num > 0){
  usleep(100);
  $this->mysqli->begin_transaction();
  $this->mysqli->query("UPDATE goods SET num=num-1 WHERE num>0");
  $affected_rows = $this->mysqli->affected_rows;
  if($affected_rows == 1){
    $this->mysqli->query("INSERT INTO log(good_id) VALUES({$num})");
    $affected_rows = $this->mysqli->affected_rows;
    if($affected_rows == 1){
      $this->mysqli->commit();
      echo "success:".$num;
    }else{
      $this->mysqli->rollback();
      echo "fail1:".$num;
    }
  }else{
    $this->mysqli->rollback();
    echo "fail2:".$num;
  }
}else{
  echo "fail3:".$num;
}

Solution based on redis

Optimistic Lock Scheme Based on watch

watch is used to monitor one (or more) key, and if this (or these) key is changed by another command before the transaction is executed, the transaction will be interrupted. This scheme is similar to the optimistic lock scheme in mysql, and its specific performance is also 1.

$num = $this->redis->get('num');
if($num > 0) {
  $this->redis->watch('num');
  usleep(100);
  $res = $this->redis->multi()->decr('num')->lPush('result',$num)->exec();
  if($res == false){
    echo "fail1";
  }else{
    echo "success:".$num;
  }
}else{
  echo "fail2";
}

Queue Scheme Based on list

The queue-based scheme takes advantage of the atomicity of redis queue-out operation. Before snapping up, the commodity number is first put into the response queue, and the operation is popped up from the queue in turn during snapping up, which can ensure that each commodity can only be obtained and operated by one process, and there is no overshoot. The advantage of this scheme is that it is simple to understand and implement, but the disadvantage is that when the number of goods is large, a large amount of data needs to be stored in the queue, and different goods need to be stored in different message queues.

public function init(){
  $this->redis->del('goods');
  for($i=1;$i<=10;$i++){
    $this->redis->lPush('goods',$i);
  }
  $this->redis->del('result');
  echo 'init done';
}
public function run(){
  $goods_id = $this->redis->rPop('goods');
  usleep(100);
  if($goods_id == false) {
    echo "fail1";
  }else{
    $res = $this->redis->lPush('result',$goods_id);
    if($res == false){
      echo "writelog:".$goods_id;
    }else{
      echo "success".$goods_id;
    }
  }
}

Scheme based on decr return value

If we set the remaining amount num to a key value type, get before judging and then decr every time can not solve the overshoot problem. However, the decr operation in redis will return the result after execution, which can solve the overshoot problem. First, we judge the values from get to num in the first step, so as to avoid updating the value of num every time, and then perform decr operation on num, and judge the return value of decr. If the return value is not less than 0, it means that decr was greater than 0 before, and the user snapped up successfully.

public function run(){
  $num = $this->redis->get('num');
  if($num > 0) {
    usleep(100);
    $retNum = $this->redis->decr('num');
    if($retNum >= 0){
      $res = $this->redis->lPush('result',$retNum);
      if($res == false){
        echo "writeLog:".$retNum;
      }else{
        echo "success:".$retNum;
      }
    }else{
      echo "fail1";
    }
  }else{
    echo "fail2";
  }
}

Exclusive Lock Scheme Based on setnx

redis does not have exclusive locks like mysql, but it can be implemented in one way, just like php uses file locks to implement exclusive locks.

setnx realizes the functions of exists and set. If a given key already exists, setnx does not do any action and returns 0; If key does not exist, perform an operation similar to set and return 1. We set a timeout time timeout, and try setnx operation every 1 fixed time. If the setting is successful, we get the corresponding lock, execute decr operation of num, delete the corresponding key after the operation, and simulate the operation of releasing the lock.

public function run(){
  do {
    $res = $this->redis->setnx("numKey",1);
    $this->timeout -= 100;
    usleep(100);
  }while($res == 0 && $this->timeout>0);
  if($res == 0){
    echo 'fail1';
  }else{
    $num = $this->redis->get('num');
    if($num > 0) {
      $this->redis->decr('num');
      usleep(100);
      $res = $this->redis->lPush('result',$num);
      if($res == false){
        echo "fail2";
      }else{
        echo "success:".$num;
      }
    }else{
      echo "fail3";
    }
    $this->redis->del("numKey");
  }
}

All the above codes have passed the local test. The complete code address is https://github.com/qianshou/SeckillSolution

For more readers interested in PHP related content, please check the topics on this site: "Introduction to php+mysql Database Operation", "Summary of php+redis Database Programming Skills", "Introduction to php Object-Oriented Programming", "Introduction to PHP Basic Syntax", "Encyclopedia of PHP Array (Array) Operation Skills", "Summary of php String (string) Usage" and "Summary of php Common Database Operation Skills"

I hope this article is helpful to everyone's PHP programming.